﻿using System;
using System.Text;
using System.Data;
using CRMFramework;
using System.Text.RegularExpressions;

namespace CRMBiz
{
    public static class ApplicationListHandler
    {
        //Function for app-list actions
        public static DataSet GetApplicationList(int pageIndex, int pageSize, string searchtext, string conditiontext, string sortText, out Int32 totalAmount, string days)
        {
            DataSet ds;
            string strdays = string.Empty;
            string afterWhereSql = string.Empty;
            sortText = sortText.Trim();
            if (sortText.Length < 0)
                sortText = string.Empty;

            conditiontext = conditiontext.Replace("%_", "%");
            
            try
            {
                var sbWhereApp = new StringBuilder(" WHERE a.deletedFlag=0 ");

                var sbWhere = new StringBuilder(" WHERE ");

                var strSql = new StringBuilder("SELECT a.app_id,CAST(a.DesiredAmount as Decimal(10,0)) as DesiredAmount ,a.app_result,a.PurposeSummary,a.LoadNo AS LoanNo, a.CreatedDate ,udf_getformatedDate(a.CreatedDate)  AS cdate,")
                    .Append(" a.EditDate,udf_getformatedDate(a.EditDate)  AS edate ,udf_SelectFirstApplicant(a.app_id) AS applicant1, udf_SelectApplicantName(a.app_id) AS SecondApplicant ,PurposeSummary ")
                    .Append(" FROM crm_application a INNER JOIN (SELECT DISTINCT ac1.app_id FROM crm_applicationcontact ac1 INNER JOIN (SELECT ContactID FROM crm_contact  ");

                afterWhereSql = " )tblAc ON tblAc.ContactID=ac1.ContactID WHERE ac1.deletedFlag=0 ) tblAcc ON tblAcc.app_id=a.app_id  ";

                var strqueryCount = new StringBuilder(" Select count(*) as total FROM crm_application a INNER JOIN (SELECT DISTINCT ac1.app_id FROM crm_applicationcontact ac1 INNER JOIN (SELECT ContactID FROM crm_contact  ");

                if (days == "30" || days == "")
                    strdays = " AND DATEDIFF(now(),a.EditDate) <= 30";
                else if (days == "90")
                    strdays = " AND DATEDIFF(now(),a.EditDate) <= 90";
                else
                    strdays = "";

                if (!string.IsNullOrEmpty(searchtext))
                {
                    String[] Words = searchtext.Split(' ');
                    if ((Words.Length == 2) && (IsNumberOnly(searchtext)) == false)
                    {
                        sbWhere.AppendFormat(" FirstName like '{0}%'", Words[0])
                            .AppendFormat(" AND SurName like '{0}%'", Words[1]);

                        if (!string.IsNullOrEmpty(conditiontext))
                        {
                            if (conditiontext.Contains("app_"))
                            {
                                sbWhereApp.Append(conditiontext);
                            }
                            else
                                sbWhere.Append(conditiontext);
                        }
                        if (!string.IsNullOrEmpty(strdays))
                            sbWhereApp.Append(strdays);

                        strSql.Append(sbWhere.ToString())
                            .Append(afterWhereSql)
                             .Append(sbWhereApp);

                        strqueryCount.Append(sbWhere.ToString())
                            .Append(afterWhereSql)
                            .Append(sbWhereApp);

                        totalAmount = Convert.ToInt32(DBhelper.ExecuteScalar(strqueryCount.ToString()));

                        if (string.IsNullOrEmpty(sortText))
                            strSql.Append(" ORDER BY a.app_id DESC");
                        else
                            strSql.AppendFormat(" ORDER BY {0}", sortText);

                        strSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(strSql.ToString());
                    }
                    else if ((Words.Length == 3) && (IsNumberOnly(searchtext)) == false)
                    {
                        sbWhere.AppendFormat(" FirstName like '{0}%'", Words[0])
                         .AppendFormat(" AND MiddleName like '{0}%'", Words[1])
                         .AppendFormat(" AND SurName like '{0}%'", Words[2]);

                        if (!string.IsNullOrEmpty(conditiontext))
                        {
                            if (conditiontext.Contains("app_"))
                            {
                                sbWhereApp.Append(conditiontext);
                            }
                            else
                                sbWhere.Append(conditiontext);
                        }
                        if (!string.IsNullOrEmpty(strdays))
                            sbWhereApp.Append(strdays);

                        strSql.Append(sbWhere.ToString())
                           .Append(afterWhereSql)
                            .Append(sbWhereApp);

                        strqueryCount.Append(sbWhere.ToString())
                          .Append(afterWhereSql)
                          .Append(sbWhereApp);

                        totalAmount = Convert.ToInt32(DBhelper.ExecuteScalar(strqueryCount.ToString()));

                        if (string.IsNullOrEmpty(sortText))
                            strSql.Append(" ORDER BY a.app_id DESC");
                        else
                            strSql.AppendFormat(" ORDER BY {0}", sortText);

                        strSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(strSql.ToString());
                    }
                    else
                    {
                        bool onlyApplication = false;
                        if (IsEmailOnly(searchtext))
                            sbWhere.AppendFormat("(EMail like '{0}%' OR secondEmailAddress like '{0}%' )", searchtext);
                        else if (IsNumberOnly(searchtext))
                        {
                            string strmob = string.Empty;
                            int len = searchtext.Length;

                            if (searchtext.Contains(" "))
                                sbWhere.AppendFormat("(Mobile like '{0}%')", searchtext);
                            else
                            {
                                if (len == 10)
                                    strmob = searchtext.Substring(0, 4) + " " + searchtext.Substring(4, 3) + " " + searchtext.Substring(7, 3);
                                else if (len > 4 && len <= 7)
                                    strmob = searchtext.Substring(0, 4) + " " + searchtext.Substring(4, len - 4);
                                else if (len > 7 && len < 10)
                                    strmob = searchtext.Substring(0, 4) + " " + searchtext.Substring(4, len - 4) + " " + searchtext.Substring(7, len - 7);
                                else if (len <= 4 || len > 10)
                                    strmob = searchtext;
                                sbWhere.AppendFormat(" (Mobile like '{0}%')", strmob);
                            }
                        }
                        else
                        {
                            if (IsAlphaNumeric(searchtext))
                            {
                                sbWhereApp.AppendFormat(" AND LoadNo like '{0}%'", searchtext);
                                onlyApplication = true;
                            }
                            else
                                sbWhere.AppendFormat("SurName like '{0}%'", searchtext);
                        }
                        if (!string.IsNullOrEmpty(conditiontext))
                        {
                            if (conditiontext.Contains("app_"))
                                sbWhereApp.Append(conditiontext);
                            else
                                sbWhere.Append(conditiontext);

                        }
                        if (!string.IsNullOrEmpty(strdays))
                            sbWhereApp.Append(strdays);

                        if (onlyApplication == false)
                        {
                            if (IsAlphaNumeric(searchtext))
                            {
                                strSql.Append(sbWhere.ToString().Replace("and", ""))
                               .Append(afterWhereSql)
                               .Append(sbWhereApp);

                                strqueryCount.Append(sbWhere.ToString().Replace("and", ""))
                                  .Append(afterWhereSql)
                                  .Append(sbWhereApp);
                            }
                            else
                            {
                                strSql.Append(sbWhere.ToString())
                                    .Append(afterWhereSql)
                                    .Append(sbWhereApp);

                                strqueryCount.Append(sbWhere.ToString())
                                  .Append(afterWhereSql)
                                  .Append(sbWhereApp);
                            }
                        }
                        else
                        {
                            strSql = new StringBuilder("Select app_id,CAST(DesiredAmount as Decimal(10,0)) as DesiredAmount ,app_result,PurposeSummary,LoadNo as LoanNo, CreatedDate,udf_getformatedDate(CreatedDate) AS cdate,EditDate,udf_getformatedDate(EditDate) AS edate ,")
                                            .Append(" udf_SelectFirstApplicant(app_id) as applicant1, udf_SelectApplicantName(app_id) as SecondApplicant ,PurposeSummary  from crm_application a")
                                            .Append(sbWhereApp.ToString());

                            strqueryCount = new StringBuilder(" Select count(*) from crm_application a ")
                                .Append(sbWhereApp.ToString());
                        }
                        totalAmount = Convert.ToInt32(DBhelper.ExecuteScalar(strqueryCount.ToString()));

                        if (string.IsNullOrEmpty(sortText))
                            strSql.Append(" ORDER BY a.app_id DESC");
                        else
                            strSql.AppendFormat(" ORDER BY {0}", sortText);

                        strSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(strSql.ToString());
                    }
                }
                else
                {
                    // Empty Advance Search text 
                    if (!string.IsNullOrEmpty(conditiontext))
                    {
                        // ONLY condition Text
                        if (conditiontext.Contains("app_"))
                        {
                            conditiontext = conditiontext.Replace("a.", "");
                            strdays = strdays.Replace("a.", "");
                            var sbSql = new StringBuilder("Select  app_id,CAST(DesiredAmount as Decimal(10,0)) as DesiredAmount ,app_result,PurposeSummary,LoadNo as LoanNo, CreatedDate,udf_getformatedDate(CreatedDate) AS cdate,EditDate,udf_getformatedDate(EditDate) AS edate ,")
                                            .Append(" udf_SelectFirstApplicant(app_id) as applicant1, udf_SelectApplicantName(app_id) as SecondApplicant,PurposeSummary from crm_application where deletedFlag=0   ");
                            string queryCount = " Select count(*) from crm_application Where deletedFlag=0 ";

                            sbSql.Append(conditiontext);
                            queryCount += conditiontext;
                            if (!string.IsNullOrEmpty(strdays))
                            {
                                sbSql.Append(strdays);
                                queryCount += strdays;
                            }

                            totalAmount = Convert.ToInt32(DBhelper.ExecuteScalar(queryCount));

                            if (string.IsNullOrEmpty(sortText))
                                sbSql.Append(" ORDER BY app_id Desc ");
                            else
                                sbSql.AppendFormat(" ORDER BY {0}", sortText.Replace("a.", ""));

                            sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                            ds = DBhelper.ExecuteDataset(sbSql.ToString());
                        }
                        else
                        {
                            conditiontext = conditiontext.Replace("and", "");
                            sbWhere.Append(conditiontext);

                            if (!string.IsNullOrEmpty(strdays))
                                sbWhereApp.Append(strdays);

                            strSql.Append(sbWhere.ToString())
                                .Append(afterWhereSql)
                                .Append(sbWhereApp);

                            strqueryCount.Append(sbWhere.ToString())
                                    .Append(afterWhereSql)
                                    .Append(sbWhereApp);

                            totalAmount = Convert.ToInt32(DBhelper.ExecuteScalar(strqueryCount.ToString()));
                            if (string.IsNullOrEmpty(sortText))
                                strSql.Append(" ORDER BY a.app_id DESC");
                            else
                                strSql.AppendFormat(" ORDER BY {0}", sortText);

                            strSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                            ds = DBhelper.ExecuteDataset(strSql.ToString());
                        }
                    }
                    else
                    {
                        //Select all data without any Search condition
                        ds = ApplicationsWithNoCondition(strdays, pageIndex, pageSize, sortText, out totalAmount);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }


        private static DataSet ApplicationsWithNoCondition(string strdays, int pageIndex, int pageSize, string sortText, out Int32 totalAmount)
        {
            DataSet ds;
            string queryCount = string.Empty;
            bool sortFlag = false;
            try
            {
                if (sortText.Contains("applicant1") || sortText.Contains("SecondApplicant"))
                    sortFlag = true;

                var sbSql = new StringBuilder("Select a.app_id,CAST(DesiredAmount as Decimal(10,0)) as DesiredAmount,app_result,PurposeSummary,LoadNo as LoanNo,CreatedDate, udf_getformatedDate(CreatedDate) AS cdate,EditDate,udf_getformatedDate(EditDate) AS edate ,")
                      .Append(" udf_SelectFirstApplicant(a.app_id) as applicant1, udf_SelectApplicantName(a.app_id) as SecondApplicant,PurposeSummary  from crm_application a INNER JOIN crm_applicationcontact ac ON a.app_id=ac.app_id WHERE a.deletedFlag=0 AND ac.deletedFlag=0 AND ac.NoInApplication=1 ");

                queryCount = " SELECT COUNT(*) FROM crm_application a INNER JOIN crm_applicationcontact ac ON a.app_id=ac.app_id WHERE a.deletedFlag=0 AND ac.deletedFlag=0 AND ac.NoInApplication=1 ";

                if (!string.IsNullOrEmpty(strdays))
                {
                    //  strdays = strdays.Replace("a.", "");
                    sbSql.Append(strdays);
                    queryCount += strdays;
                }
                totalAmount = Convert.ToInt32(DBhelper.ExecuteScalar(queryCount));
                if (sortFlag)
                {
                    sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(sbSql.ToString());

                    DataView dv = ds.Tables[0].DefaultView;
                    dv.Sort = sortText;
                    ds.Tables.RemoveAt(0);
                    ds.Tables.Add(dv.ToTable());
                }
                else
                {
                    if (string.IsNullOrEmpty(sortText))
                        sbSql.Append(" ORDER BY a.app_id Desc ");
                    else
                        sbSql.AppendFormat(" ORDER BY {0}", sortText);
                    //sbSql.AppendFormat(" ORDER BY {0}", sortText.Replace("a.", "")); 
                    sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(sbSql.ToString());
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        public static DataSet GetApplicationDetail(int appid)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", appid),
                };
                string sbSql = "Select * from app_info where app_id=@app_id";
                ds = DBhelper.ExecuteDataset(sbSql, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataTable BindApplication(int appid)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", appid),
                    DBhelper.SetParameter("@OperatorID", 1),
                    DBhelper.SetParameter("@LatestTime", DateTime.Now),
                };

                StringBuilder sqlqry = new StringBuilder("insert into crm_Recent(app_id,OperatorID,LatestTime) values(@app_id,@OperatorID,@LatestTime);")
                    .Append("select crm_application.*, crm_application.* from crm_application INNER JOIN crm_applicationcontact ON crm_application.app_id = crm_applicationcontact.app_id INNER JOIN crm_contact ON crm_applicationcontact.ContactID = crm_contact.ContactID ")
                    .Append("where crm_application.app_id=@app_id;");

                dt = DBhelper.ExecuteDataset(sqlqry.ToString(), para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        private static bool IsNumberOnly(string value)
        {
            string validChar = "0123456789 ";
            foreach (char c in value)
            {
                if (!validChar.Contains(c.ToString()))
                    return false;
            }
            return true;
        }

        private static bool IsEmailOnly(string value)
        {
            var regex = new Regex(@"[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?");
            return regex.IsMatch(value);
        }

        public static bool IsAlphaNumeric(string value)
        {
            Regex rg = new Regex(@"^\w*(?=\w*\d)(?=\w*[a-zA-z])\w*$");
            return rg.IsMatch(value);
        }
    }
}
